package com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.model.StudentInfo;
import com.model.Class;
import com.util.DBUtil;
import com.util.ModeStudent;

public class StudentInfoDao {
	Connection conn;
	PreparedStatement ps;
	ResultSet rs;
	
	public int login(StudentInfo stu) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("select count(*) from studentInfo where stuphone=? and stupwd=? and isdelete=1 or stustatus=? and stupwd=? and isdelete=1");
			ps.setString(1, stu.getStuStatus());
			ps.setString(2, stu.getStuPwd());
			ps.setString(3, stu.getStuStatus());
			ps.setString(4, stu.getStuPwd());
			rs=ps.executeQuery();
			if(rs.next()) {
				row=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int insertAll(StudentInfo stu) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("insert into studentInfo values(null,?,?,?,?,?,?,1)");
			ps.setInt(1, stu.getClassId());
			ps.setString(2, stu.getStuName());
			ps.setString(3, stu.getStuPwd());
			ps.setString(4, stu.getStuSex());
			ps.setString(5, stu.getStuPhone());
			ps.setString(6, stu.getStuStatus());
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int deleteById(int id) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("update studentInfo set IsDelete=0 where StuId=?");
			ps.setInt(1, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public int updateBuId(StudentInfo stu) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("update studentInfo set ClassId=?,StuName=?,StuPwd=?,stuSex=?,StuPhone=?,StuStatus=? where StuId=?");
			ps.setInt(1, stu.getClassId());
			ps.setString(2, stu.getStuName());
			ps.setString(3, stu.getStuPwd());
			ps.setString(4, stu.getStuSex());
			ps.setString(5, stu.getStuPhone());
			ps.setString(6, stu.getStuStatus());
			ps.setInt(7, stu.getStuId());
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	
	public int updatepwdById(String pwd,int id) {
		int row=-1;
		conn=DBUtil.getConn();
		try {
			ps=conn.prepareStatement("update studentInfo set stupwd=? where StuId=?");
			ps.setString(1, pwd);
			ps.setInt(2, id);
			row=ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return row;
	}
	
	public ModeStudent selectAll(int page,int rows,String name,int bj) {
		ModeStudent m=new ModeStudent();
		ArrayList<StudentInfo> list=new ArrayList<StudentInfo>();
		conn=DBUtil.getConn();
		page=(page-1)*rows;
		String sql="select studentInfo.*,classInfo.ClassName from studentInfo,classInfo where studentInfo.IsDelete=1 and studentInfo.classId=classInfo.classId limit ?,?";
		if(name!=null&&!"".equals(name)&&bj==0) {
			sql="select studentInfo.*,classInfo.ClassName from studentInfo,classInfo where studentInfo.IsDelete=1 and studentInfo.classId=classInfo.classId and StuName like'%"+name+"%' limit ?,?";
		}else if(bj!=0){
			sql="select studentInfo.*,classInfo.ClassName from studentInfo,classInfo where studentInfo.IsDelete=1 and studentInfo.classId=classInfo.classId and classInfo.classId="+bj+" and StuName like'%"+name+"%' limit ?,?";
		}
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, page);
			ps.setInt(2, rows);
			rs=ps.executeQuery();
			while(rs.next()) {
				StudentInfo stu=new StudentInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(9));
				list.add(stu);
			}
			m.setRows(list);
			m.setTotal(selectCount(name,bj));
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return m;
	}
	
	public int selectCount(String name,int bj) {
		int count=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from studentInfo where IsDelete=1";
		if(name!=null&&!"".equals(name)&&bj==0) {
			sql="select count(*) from studentInfo where IsDelete=1 and StuName like'%"+name+"%'";
		}else if(bj!=0){
			sql="select count(*) from studentInfo,classInfo where studentInfo.IsDelete=1 and studentInfo.classId=classInfo.classId and classInfo.classId="+bj+" and StuName like'%"+name+"%'";
		}
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return count;
	}
	
	public ArrayList<StudentInfo> selectAll(String name) {
		ArrayList<StudentInfo> list=new ArrayList<StudentInfo>();
		conn=DBUtil.getConn();
		String sql="select studentInfo.*,classInfo.ClassName from studentInfo,classInfo where studentInfo.IsDelete=1 and studentInfo.classId=classInfo.classId and classInfo.ClassName=?";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, name);
			rs=ps.executeQuery();
			while(rs.next()) {
				StudentInfo stu=new StudentInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(9));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	
	public int selectByQbBjCount() {
		int count=0;
		conn=DBUtil.getConn();
		String sql="select count(*) from (select count(*) from studentInfo group by ClassId)stu";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return count;
	}
	
	public ArrayList<Class> selectByClassName() {
		ArrayList<Class> list=new ArrayList<Class>();
		conn=DBUtil.getConn();
		String sql="select classInfo.ClassName from studentInfo,classInfo where classInfo.classId=studentInfo.classId group by studentInfo.classId order by classInfo.ClassName;";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				Class c=new Class();
				c.setClassName(rs.getString(1));
				list.add(c);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	public ArrayList<StudentInfo> selectByQbXueSheng() {
		ArrayList<StudentInfo> list=new ArrayList<StudentInfo>();
		conn=DBUtil.getConn();
		String sql="select studentInfo.*,classInfo.ClassName from studentInfo,classInfo where classInfo.ClassId=studentInfo.ClassId order by classInfo.ClassName";
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				StudentInfo stu=new StudentInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(9));
				list.add(stu);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return list;
	}
	
	
	public StudentInfo selectAlloneByNamePwd(StudentInfo s) {
		StudentInfo stu=null;
		conn=DBUtil.getConn();
		String sql="select studentInfo.*,classInfo.ClassName from studentInfo,classInfo where studentInfo.IsDelete=1 and studentInfo.classId=classInfo.classId and stupwd=? and (stuphone=? or stustatus=?);";
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, s.getStuPwd());
			ps.setString(2, s.getStuStatus());
			ps.setString(3, s.getStuStatus());
			rs=ps.executeQuery();
			if(rs.next()) {
				stu=new StudentInfo(rs.getInt(1),rs.getInt(2),rs.getString(3),rs.getString(4),rs.getString(5),rs.getString(6),rs.getString(7),rs.getString(9));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBUtil.close(rs, ps, conn);
		}
		return stu;
	}
}
